USE [NLKHCNData_Prod]
GO

/****** Object:  StoredProcedure [dbo].[SearchChuyenGiaAdvanceCount]    Script Date: 1/4/2015 11:47:28 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- 2015-1-4: hien created
-- EXEC SearchChuyenGiaAdvanceCount '', '', '', 0, 4, '',''
-- =============================================

CREATE PROCEDURE [dbo].[SearchChuyenGiaAdvanceCount] 
	@hoTen nvarchar(255) = '',
	@tenDonVi nvarchar(255) = '',
	@diaChiDonVi nvarchar(255) = '',
	@linhVucId int = 0,
	@nhomLinhVucId int = 0,
	@tenLuanAn nvarchar(255) = '',
	@tenCongTrinh nvarchar(255) = ''
AS
BEGIN

select count(*)
from ChuyenGiaCongTrinhView CG
	left outer join LinhVuc LV on CG.LinhVucId = LV.Id
	left outer join NhomLinhVuc NLV on LV.NhomLinhVucId = NLV.Id
where 
	(
		(@linhVucId = 0 and @nhomLinhVucId = 0)
		or (@linhVucId > 0 and LinhVucId = @linhVucId) 
		or (@linhVucId = 0 and @nhomLinhVucId > 0 and NLV.Id=@nhomLinhVucId)
	)
	and
	((@hoTen = '') or (HoTen like N'%' + @hoTen + '%')) and
	((@tenDonVi = '') or (TenDonVi like N'%' + @tenDonVi + '%')) and
	((@diaChiDonVi = '') or (DiaChiDonVi like N'%' + @diaChiDonVi + '%')) and
	((@tenLuanAn = '') or (TenLuanAn like N'%' + @tenLuanAn + '%')) and
	((@tenCongTrinh = '') or (CongTrinh like N'%' + @tenCongTrinh + '%'))
	
END



GO


